# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
from twisted.enterprise import adbapi
import MySQLdb
import MySQLdb.cursors
import codecs
import json



class JsonWithTurorialPipeline(object):
    '''保存到文件中对应的class
       1、在settings.py文件中配置
       2、在自己实现的爬虫类中yield item,会自动执行'''
    def __init__(self):
        self.file = codecs.open('info.json', 'w', encoding='utf-8')#保存为json文件
    def process_item(self, item, spider):
        line = json.dumps(dict(item),ensure_ascii=False) + "\n"#转为json的
        self.file.write(line)#写入文件中
        return item
    def spider_closed(self, spider):#爬虫结束时关闭文件
        self.file.close()

#保存到数据库中对应的class
class MysqlWithTurorialPipeline(object):
    def __init__(self,dbpool):
        self.dbpool = dbpool
    @classmethod
    def from_settings(cls,settings):
        dbparams=dict(
            host=settings['MYSQL_HOST'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWD'],
            charset='utf8',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        dbpool=adbapi.ConnectionPool('MySQLdb',**dbparams)
        return cls(dbpool)

    def process_item(self, item, spider):
        # query=self.dbpool.runInteraction(self._conditional_insert_quotes,item)
        query = self.dbpool.runInteraction(self._conditional_insert_agency, item)
        query.addErrback(self._handle_error,item,spider)
        return item
    ##quote插入到数据库中去
    def _conditional_insert_quotes(self,tx,item):
        sql="insert into quotes(title,author,tags) values (%s,%s,%s)"
        params=(item['title'],item['author'],item['tags'])
        tx.execute(sql,params)
    ###agency插入到数据库中去
    def _conditional_insert_agency(self,tx,item):
        sql = "insert into agency(ipaddress,place,lifeTime,port) values (%s,%s,%s,%s)"
        params = (item['ipaddress'], item['place'], item['life'],item['port'])
        # print('----------mysql--------')
        # print('ipaddress:%s---port:%s----place:%s-----life:%s' % (item['ipaddress'], item['port'], item['place'], item['life']))
        tx.execute(sql, params)
    def _handle_error(self, failue, item, spider):
        print('--------------database operation exception!!-----------------')
        print('-------------------------------------------------------------')
        print(failue)